{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sqlalchemy import create_engine\n",
    "plt.rcParams['font.sans-serif'] = ['KaiTi']\n",
    "plt.rcParams['font.serif'] = ['KaiTi']\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine('sqlite:///D:/sqlite/Test.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1671\n",
      "价格     1\n",
      "面积     1\n",
      "编号     1\n",
      "户型     1\n",
      "楼层     1\n",
      "位置1    1\n",
      "位置2    1\n",
      "小区     1\n",
      "地铁     1\n",
      "dtype: int64\n",
      "价格               1920.0\n",
      "面积                 17.0\n",
      "编号             121707-C\n",
      "户型                 3室1卫\n",
      "楼层                 5/6层\n",
      "位置1                 通州区\n",
      "位置2                北运河西\n",
      "小区              玉桥中路2号院\n",
      "地铁     地铁：距6号线北运河西站950米\n",
      "Name: 847, dtype: object\n",
      "<bound method DataFrame.info of           价格     面积        编号    户型      楼层  位置1     位置2       小区  \\\n",
      "0     1330.0    9.0   38738-A  3室1卫   9/11层  房山区  良乡大学城西     紫汇家园   \n",
      "1     5810.0   39.0   64752-A  1室1卫   9/12层  东城区    广渠门内      绿景苑   \n",
      "2     4820.0   36.0   65359-A  1室1卫    1/6层  东城区      景泰      定安里   \n",
      "3     1850.0    9.0     185-B  4室2卫   2/27层  朝阳区      管庄  京通苑阳光华苑   \n",
      "4     9000.0  116.0   17830-A  2室1卫   8/16层  朝阳区     十里堡     天天朝阳   \n",
      "...      ...    ...       ...   ...     ...  ...     ...      ...   \n",
      "6020  1600.0   12.0   79984-B  4室1卫    4/6层  通州区    北运河西     运乔嘉园   \n",
      "6021  4760.0   37.0  150161-A  1室1卫   9/24层  丰台区     刘家窑     远中悦麒   \n",
      "6022  5920.0   34.0   49913-A  1室1卫   6/12层  东城区    广渠门内      绿景苑   \n",
      "6023  2900.0   15.0   62649-C  4室1卫  24/25层  朝阳区      四惠      力源里   \n",
      "6024  2000.0   13.0   69646-A  3室1卫   3/21层  朝阳区    东坝中街      东泽园   \n",
      "\n",
      "                   地铁  所在楼层  总楼层  地铁数  距离地铁距离  \n",
      "0     距房山线良乡大学城西站550米     9   11    1     550  \n",
      "1       距7号线广渠门内站650米     9   12    1     650  \n",
      "2      距14号线东段景泰站450米     1    6    1     450  \n",
      "3         距八通线管庄站500米     2   27    1     500  \n",
      "4        距6号线十里堡站850米     8   16    1     850  \n",
      "...               ...   ...  ...  ...     ...  \n",
      "6020   距6号线北运河西站1000米     4    6    1    1000  \n",
      "6021     距5号线刘家窑站950米     9   24    1     950  \n",
      "6022    距7号线广渠门内站650米     6   12    1     650  \n",
      "6023  距1号线,八通线四惠站250米    24   25    2     250  \n",
      "6024                      3   21    0      -1  \n",
      "\n",
      "[6024 rows x 13 columns]>\n"
     ]
    }
   ],
   "source": [
    "dir=\"D:/github数据分析/RentFromDanke\"\n",
    "data_list=[]\n",
    "for i in range(1,9):\n",
    "    data=pd.read_csv(f\"{dir}/bj_danke_{i}.csv\")\n",
    "    data_list.append(data)\n",
    "df=pd.concat(data_list)\n",
    "print(df.duplicated().sum())\n",
    "df.drop_duplicates(inplace=True)\n",
    "print(df.isnull().sum())\n",
    "df.dropna(inplace=True,axis=0)\n",
    "df.reset_index(drop=True,inplace=True)\n",
    "print(df.loc[847,:])\n",
    "df=df[df['户型']!='户型']\n",
    "df['地铁']=df['地铁'].apply(lambda x :x.replace(\"地铁：\",\"\"))\n",
    "df['所在楼层'] = df['楼层'].apply(lambda x: int(x.split('/')[0]))\n",
    "df[ '总楼层'] = df['楼层'].apply(lambda x: int(x.replace('层', '').split('/')[-1]))\n",
    "df['地铁数'] = df['地铁'].apply(lambda x: len(re.findall('线', x)))\n",
    "df['距离地铁距离'] = df['地铁'].apply(lambda x: int(re.findall('(\\d+)米', x)[-1]) if re.findall('(\\d+)米', x) else -1)\n",
    "print(df.info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6024"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_sql('rent', con=engine, index=False, if_exists='append')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
